package com.qf.mapper;

import com.qf.vo.CategorySellAmts;
import com.qf.vo.DayStatis;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 首页统计接口
 */
public interface IndexTotalMapper {
    /**
     * 统计商品分类和对应分类的销售额
     *
     * @return
     */
    @Select("SELECT  c.`name` ,SUM(o.actual_price) as `value`   " +
            "from dts_category c " +
            "left join dts_goods g " +
            "on c.id=g.category_id  " +
            "LEFT JOIN dts_order_goods og " +
            "ON g.id=og.goods_id " +
            "LEFT JOIN dts_order o " +
            "on og.order_id=o.id " +
            "WHERE o.actual_price is not null " +
            "GROUP BY c.`name` ")
    List<CategorySellAmts> categorySellCount();

    /**
     * 日期和对应的订单总额, 订单笔数
     *
     * @return
     */
    @Select("SELECT  " +
            "DATE_FORMAT(o.add_time,\"%Y-%m-%d\") as dayStr, " +
            "COUNT(*) as cnts, " +
            "SUM(o.actual_price) as amts " +
            "FROM dts_order o  " +
            "where o.deleted=0 " +
            "GROUP BY DATE_FORMAT(o.add_time,\"%Y-%m-%d\") " +
            "ORDER BY dayStr")
    List<DayStatis> orderAmts();

    /**
     * 统计日期和对应的用户数增长量
     */
    @Select("SELECT  " +
            "DATE_FORMAT(u.add_time,\"%Y-%m-%d\") as dayStr, " +
            "COUNT(*) as cnts " +
            "FROM dts_user u " +
            "GROUP BY DATE_FORMAT(u.add_time,\"%Y-%m-%d\") " +
            "ORDER BY dayStr")
    List<DayStatis> userCnts();



}
